#-------------------------------------------------------------------------------
# Name:        d4dset1's utility functions
# Purpose:     data source: mysql database d4d 
#
# Author:      Leye
#
# Created:     18/12/2012
# Copyright:   (c) Leye 2012
# Licence:     MIT
#-------------------------------------------------------------------------------

import pymysql as db
#import MySQLdb as db
from datetime import date
from datetime import timedelta

conn = db.connect(
        host='127.0.0.1', port=3306, user='root', passwd='wlywly', db='d4d')

START_DATE = date(2011, 12, 5)

####following arecommon functions

def execute_query_one_value(query):
    cur = conn.cursor()
    cur.execute(query) 
    result = cur.fetchone()
    value = None
    if result is not None:
       value = result[0] 
    cur.close()
    return value

def execute_query_multi_value(query):
    cur = conn.cursor()

    cur.execute(query)
    result = cur.fetchone()
    cur.close()
    return result

def execute_query_fetch_all(query):
    cur = conn.cursor()
    cur.execute(query) 
    result = cur.fetchall() 
    cur.close()
    return result

def date_range(start_date, end_date):
    dates_set = []
    diff = (end_date - start_date).days
    for i in range(diff+1):
        dates_set.append(start_date+timedelta(days=i))
    return dates_set

def days_after_start(date_query):
    time_diff = date_query - START_DATE
    return time_diff.days

def count_table_suffix(date_query):
    days = days_after_start(date_query)
    return days/14 #each table has 2 weeks

#following methods for set1 analysis

def sum_ant_related_all_calls(ant_query, date_query):
    """
    count all the calls from or to the antenna in one day
    """
    table_suffix = count_table_suffix(date_query)
    #following sql runs more quickly in MySQL 5.5, though it's somewhat ugly
    query = """
        select sum(nb_voice_calls) 
        from h_a_flows_{table_num} force index(PRIMARY, idx_ter_ant_datehour)
        where 
        (originating_ant={ant_id} 
        and date_hour between '{date} 00:00:00' and '{date} 23:59:59')
        or
        (terminating_ant={ant_id}
        and date_hour between '{date} 00:00:00' and '{date} 23:59:59')
        """.format(table_num=table_suffix, ant_id=ant_query, date=date_query)
    return execute_query_one_value(query)

def sum_ant_self_calls_day(ant_query, date_query):
    """
    count all the calls from and to the same antenna in one day.
    """
    table_suffix = count_table_suffix(date_query)
    query = """
        select sum(nb_voice_calls)
        from h_a_flows_{table_num}
        where originating_ant={ant_id} and terminating_ant={ant_id}
        and date_hour between '{date} 00:00:00' and '{date} 23:59:59'
        """.format(table_num=table_suffix, ant_id=ant_query, date=date_query)
    return execute_query_one_value(query)

def sum_ant_org_calls_day(ant_query, date_query):
    """
    count all the calls from one anntenna in one day.
    """
    table_suffix = count_table_suffix(date_query)
    query = """
        select sum(nb_voice_calls)
        from h_a_flows_{table_num}
        where originating_ant={ant_id} 
        and date_hour between '{date} 00:00:00' and '{date} 23:59:59'
        """.format(table_num=table_suffix, ant_id=ant_query, date=date_query)
    return execute_query_one_value(query)

def sum_ant_ter_calls_day(ant_query, date_query):
    """
    count all the calls to one anntenna in one day.
    """
    table_suffix = count_table_suffix(date_query)
    query = """
        select sum(nb_voice_calls)
        from h_a_flows_{table_num}
        where terminating_ant={ant_id} 
        and date_hour between '{date} 00:00:00' and '{date} 23:59:59'
        """.format(table_num=table_suffix, ant_id=ant_query, date=date_query)
    return execute_query_one_value(query)

def count_ant_self_records_day(ant_query, date_query):
    table_suffix = count_table_suffix(date_query)
    query = """
        select count(*)
        from h_a_flows_{table_num}
        where originating_ant={ant_id} and terminating_ant={ant_id}
        and date_hour between '{date} 00:00:00' and '{date} 23:59:59'
        """.format(table_num=table_suffix, ant_id=ant_query, date=date_query)
    return execute_query_one_value(query)

def count_day_valid_hours(date_query):
    table_suffix = count_table_suffix(date_query)
    query = """
    	select count(*) as hours, sum(nb_voice_calls) as calls
    	from h_a_flows_{table_num}
    	where originating_ant=terminating_ant and originating_ant<>-1
    	and date_hour between '{date} 00:00:00' and '{date} 23:59:59'
        """.format(table_num=table_suffix, date=date_query)
    return execute_query_multi_value(query)

def count_day_total_calls(date_query):
    table_suffix = count_table_suffix(date_query)    
    query = """
    	select sum(nb_voice_calls) as calls
    	from h_a_flows_{table_num}
    	where date_hour between '{date} 00:00:00' and '{date} 23:59:59'
        """.format(table_num=table_suffix, date=date_query)
    return execute_query_one_value(query)


#following methods for set2
def count_distinct_caller(antenna_set, start_datetime, end_datetime, min_call):
    """
    count different users who make calls more than min_call times
    during a time period and in a restricted district
    """
    table_suffix = 0 #### TODO need to change in the future, maybe
    
    area = ",".join(str(x) for x in antenna_set)
    query = """
            select count(t.user_id) from (
                select user_id, count(antenna_id) as call_number
                from ant_pos_sample_0
                where antenna_id in ({ant_set})
                and connection_datetime between '{start}' and '{end}'
                group by user_id
                order by call_number desc) as t
            where t.call_number >= {mcall} 
            """.format(ant_set=area, start=start_datetime, end=end_datetime,
                    mcall=min_call)
    return execute_query_one_value(query)

def select_caller_with_first_call_time(antenna_set, start_datetime, end_datetime):
    """
    select different users with their first call's time 
    during a time period and in a district
    """
    table_suffix = 0 ## TODO 

    area = ",".join(str(x) for x in antenna_set)
    query = """
            select user_id, min(connection_datetime) as min_time
            from ant_pos_sample_0
            where antenna_id in ({ant_set})
            and connection_datetime between '{start}' and '{end}'
            group by user_id
            order by min_time
            """.format(ant_set=area, start=start_datetime, end=end_datetime)
    #print query
    return execute_query_fetch_all(query)

def query_first_call_time(user, antenna_set, start_datetime, end_datetime):
    """
    select the first time when one user make a call in a time period
    """
    table_suffix = 0 ##TODO

    area = ",".join(str(x) for x in antenna_set)
    query = """
            select connection_datetime
            from ant_pos_sample_0
            where antenna_id in ({ant_set})
            and connection_datetime between '{start}' and '{end}'
            and user_id = {user_id} 
            order by connection_datetime asc
            limit 1
            """.format(ant_set=area, start=start_datetime, end=end_datetime, user_id=user)
    return execute_query_one_value(query)

def select_top_frequency_caller(antenna_set, topn, start_hour=0, end_hour=23):
    table_suffix = 0 ##TODO
    area = ",".join(str(x) for x in antenna_set)
    query = """
            select user_id, count(*) as call_times
            from ant_pos_sample_0
            where antenna_id in ({ant_set})
            and HOUR(connection_datetime) between {s} and {e}
            group by user_id
            order by call_times desc
            limit {n}
            """.format(ant_set=area, n=topn, s=start_hour, e=end_hour)
    #print query
    return execute_query_fetch_all(query)



def main(ant_id):
    for i in range(0,140):  #all 10 weeks
        date_query = START_DATE + timedelta(days=i)
        calls = sum_ant_self_calls_day(ant_id, date_query)
        records_count = count_ant_self_records_day(ant_id, date_query)
        print('{}\t{}\t{}'.format(date_query, calls, records_count))

def statistic_average_valid_hours_over_all_ants():
    for i in range(0,140):  #all 10 weeks
        date_query = START_DATE + timedelta(days=i)
        valid_hours, calls_sum = count_day_valid_hours(date_query)
        print('{}\t{}\t{}'.format(date_query, calls_sum/1238, valid_hours/1238))  #total 1238 ants


def statistic_total_calls_every_day():
    for i in range(0,140):  #all 10 weeks
        date_query = START_DATE + timedelta(days=i) 
        calls_sum = count_day_total_calls(date_query)
        print('{}\t{}'.format(date_query, calls_sum))

if __name__ == '__main__':
    #main(-1)
    #statistic_average_valid_hours_over_all_ants()
    statistic_total_calls_every_day()
    conn.close()
